﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_leave_save')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_leave_save

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-21 00:24:02
-- Database:    [scm_main-hysj2]
-- Description: 请假管理-请假申请保存
-- =============================================

CREATE PROCEDURE bd_proc_leave_save
  @leaveJson VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT *
  INTO #params
  FROM OPENJSON(@leaveJson)
  WITH (
  orderNo VARCHAR(20),
  orderId INT,
  startDate DATE,
  endDate DATE,
  startTime INT,
  endTime INT,
  leaveType VARCHAR(20),
  leaveDuration INT,
  firstApprover BIGINT,
  secondApprover BIGINT,
  userIds VARCHAR(MAX),
  applyType INT,
  remark VARCHAR(2000),
  ip VARCHAR(20),
  operateId BIGINT
  )

  CREATE TABLE #leave
  (
    user_serial BIGINT,
    user_no VARCHAR(50),
    user_name VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
    dep_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
    leave_date DATE,
    leave_duration INT DEFAULT 0,
    vacation_annual INT DEFAULT 0,
    vacation_lactational INT DEFAULT 0,
    vacation_rest INT DEFAULT 0,
    work_calendar INT DEFAULT 0,
    work_status INT DEFAULT 0,
    shift_id INT DEFAULT 0,
    shift_name  VARCHAR(100),
    same_type INT DEFAULT 0,
    is_over INT DEFAULT 0,    --每天请假是否超过8小时

    cur_year INT DEFAULT 0,
    cur_month INT DEFAULT 0,
    cur_ym_num INT DEFAULT 0,

    settle_year INT DEFAULT 0,        --结算年份
    settle_month INT DEFAULT 0,        --结算月份
    settle_ym_num INT DEFAULT 0,
    settle_ym DATE,     --封账年月，格式年月日，日不做计算

    lactational_date_begin DATE,   --授乳假开始日期
    lactational_date_end DATE,     --授乳假结束日期
    lactational_expire INT DEFAULT 0,  --授乳假是否过期 
    --    settle_date_begin DATE,   --结算开始日期
    --    settle_date_end DATE,     --结算结束日期
    --    settle_year_begin INT,    --结算开始年份
    --    settle_year_end INT,      --结算结束年份
    --    settle_month_begin  INT,  --结算开始月份
    --    settle_month_end  INT,    --结算结束月份

    is_lock INT DEFAULT 0   --是否封账
  )

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
  )

  DECLARE @orderNo VARCHAR(50)
         ,@orderId BIGINT
         ,@operateId BIGINT
         ,@pcIp VARCHAR(20)
         ,@startDate DATE
         ,@endDate DATE
         ,@startTime INT
         ,@endTime INT
         ,@leaveType VARCHAR(20)
         ,@leaveDuration INT
         ,@userIds VARCHAR(MAX)
         ,@firstApprover BIGINT
         ,@secondApprover BIGINT
         ,@applyType INT
         ,@remark VARCHAR(MAX)

  SELECT TOP 1 @orderNo = orderNo, @orderId = orderId, @operateId = operateId, @startDate = startDate, @endDate = endDate,
   @startTime = startTime, @endTime = endTime, @leaveType = leaveType, @leaveDuration = leaveDuration, @userIds = userIds,
   @firstApprover = firstApprover, @secondApprover = secondApprover, @applyType = applyType, @remark = remark, @pcIp = ip
  FROM #params


  DECLARE @userInfo TABLE
  (
    user_serial BIGINT,
    user_sex VARCHAR(20),
    lactational_begin DATE,
    lactational_end DATE,
    vacation_duration INT DEFAULT 0,    --假期额度 年假、调休、授乳假
    duration_total INT DEFAULT 0        --申请额度
  )


  INSERT INTO @userInfo(user_serial, user_sex, lactational_begin, lactational_end)
  SELECT value, user_sex, lactational_begin, lactational_end
  FROM STRING_SPLIT(@userIds, ',') ids
  INNER JOIN (
      SELECT
        user_serial, user_sex, lactational_begin, lactational_end
      FROM dt_user
    ) u ON u.user_serial = ids.value


  IF NOT EXISTS (
      SELECT
        1
      FROM @userInfo
    )
  BEGIN
    INSERT INTO #result(error_msg)
    VALUES ('请假人不能为空')

    SELECT * FROM #result

    RETURN
  END

  --请假假类时长和增量检测
  DECLARE @minDuration INT
         ,@increase INT
         ,@leaveTypeName VARCHAR(50)
         ,@diff INT
         ,@unit INT
         ,@unitName VARCHAR(20)

  SELECT @minDuration = minsj, @increase = multiplesj, @leaveTypeName = mc, @unit = multipledw
  FROM kt_qingj
  WHERE bh = @leaveType

  IF @leaveDuration < @minDuration
  BEGIN
    INSERT INTO #result(error_msg)
    VALUES (@leaveTypeName + '请假时间不能少于' + CAST(@minDuration AS VARCHAR))

    SELECT * FROM #result

    RETURN
  END

  SET @diff = @leaveDuration - @minDuration
  SELECT @unitName = CASE WHEN ISNULL(@unit, 0) = 0 THEN '分钟' ELSE '小时' END

  IF @diff % @increase != 0
  BEGIN
    INSERT INTO #result(error_msg)
    VALUES (@leaveTypeName + '请假递增不能少于' + CAST(@increase AS VARCHAR) + @unitName)

    SELECT * FROM #result

    RETURN
  END

  --生成日期
  DECLARE @date TABLE
  (
    date_day DATE
  )

  DECLARE @dateFrom DATETIME = @startDate
  DECLARE @dateTo DATETIME = @endDate
  ;
  WITH tmpDays
  AS
  (
    SELECT
      @dateFrom date_value
    UNION ALL
    SELECT
      date_value + 1
    FROM tmpDays
    WHERE date_value + 1 <= @dateTo
  )
  INSERT INTO @date(date_day)
  SELECT CONVERT(VARCHAR(10), date_value, 120)
  FROM tmpDays
  OPTION (MAXRECURSION 0);


  DECLARE @total INT
  SELECT @total = COUNT(1) FROM @userInfo


  INSERT INTO #leave(user_serial, leave_date, leave_duration, vacation_annual, vacation_lactational, vacation_rest)
  SELECT user_serial, date_day, @leaveDuration, 0, 0, 0 FROM @userInfo, @date

  UPDATE lv
  SET shift_id = sch.shift_id
  FROM #leave lv
  INNER JOIN bd_kq_schedule_detail sch ON sch.user_serial = lv.user_serial AND sch.shift_date = lv.leave_date

  --工作日历
  UPDATE lv
  SET work_calendar = u.user_work_calendar
     ,user_no = u.user_no
     ,user_name = u.user_lname
     ,lv.dep_name = u.dep_name
  FROM #leave lv
  INNER JOIN bd_view_user_all u ON u.user_serial = lv.user_serial

  UPDATE lv
  SET work_status =
    CASE
      WHEN wc.is_weekend = 1 THEN
        1
      WHEN wc.is_statutory = 1 THEN
        2
      ELSE
        0
    END
  FROM #leave lv
  INNER JOIN bd_kq_work_calendar_detail wc ON wc.parent_id = lv.work_calendar AND wc.work_date = lv.leave_date

--  INSERT INTO #result(user_serial, error_msg)
--  SELECT DISTINCT user_serial, CONVERT(VARCHAR(10), leave_date, 120) + '休息日不用申请请假'
--  FROM #leave
--  WHERE work_status > 0

  --删除非工作日
  DELETE FROM #leave
  WHERE work_status > 0


  --封账检测
  UPDATE #leave
  SET settle_ym = dbo.bd_fn_getSettleDate(leave_date)

  UPDATE #leave
  SET cur_year = YEAR(settle_ym)
     ,cur_month = MONTH(settle_ym)
  UPDATE #leave
  SET cur_ym_num = CAST(CAST(cur_year AS VARCHAR(4)) + RIGHT('00' + CAST(cur_month AS VARCHAR(2)), 2) AS INT)

  --最后结算月份
  UPDATE lv
  SET settle_year = x.time_year
     ,settle_month = x.time_month
  FROM #leave lv
  INNER JOIN (
      SELECT
        *
      FROM bd_view_kq_holidays_time_rowno
      WHERE row_no = 1
    ) x ON x.user_serial = lv.user_serial

  UPDATE #leave
  SET settle_ym_num = CAST(CAST(ISNULL(cur_year, 0) AS VARCHAR(4)) + RIGHT('00' + CAST(ISNULL(cur_month, 0) AS VARCHAR(2)), 2) AS INT)

  --申请月份是否封账
  UPDATE lv
  SET is_lock = 1
  FROM #leave lv
  INNER JOIN bd_kq_accounts ac ON ac.user_serial = lv.user_serial AND ac.year = lv.cur_year AND ac.month = lv.cur_month AND ISNULL(ac.lock_status, 0) > 0

  --申请月份小于当前结算月份，表示申请的日期已经封账
  UPDATE #leave
  SET is_lock = 1
  WHERE cur_ym_num - settle_ym_num < 0

  INSERT INTO #result(user_serial, error_msg)
  SELECT DISTINCT user_serial, CAST(settle_year AS VARCHAR) + '-' + RIGHT('00' + CAST(settle_month AS VARCHAR), 2) + '已经封账,不能请假'
  FROM #leave
  WHERE is_lock = 1

  DELETE FROM #leave
  WHERE is_lock = 1

  --10002	调休假
  --最后结算月份未HR封账时，不能申请调休假
  --当月月份-当前结算月份>=2，不能申请
  IF @leaveType = '10002'
  BEGIN

    --最后结算月份是否HR封账
    --HR封账的才可以申请调休
    UPDATE lv
    SET is_lock = 1
    FROM #leave lv
    INNER JOIN bd_kq_accounts ac ON ac.user_serial = lv.user_serial AND ac.year = lv.settle_year AND ac.month = lv.settle_month AND ISNULL(ac.lock_status, 0) = 2

    --申请月份超过结算月份2个月以上不能申请调休
    UPDATE #leave
    SET is_lock = 0
    WHERE ABS(DATEDIFF(MONTH, leave_date, DATEFROMPARTS(settle_year, settle_month, 1))) >= 2 AND is_lock = 1

    INSERT INTO #result(user_serial, error_msg)
    SELECT DISTINCT user_serial, CAST(cur_year AS VARCHAR) + '-' + RIGHT('00' + CAST(cur_month AS VARCHAR), 2) + '未封账,不能请调休假'
    FROM #leave
    WHERE is_lock = 0

    DELETE FROM #leave
    WHERE is_lock = 0
  END



  --性别假类检测 

  --10014	产检假	仅限女性员工
  --10015	产假	仅限女性员工
  --10014	产前假	仅限女性员工
  --10013	产前（休息）假	仅限女性员工
  --10016	哺乳假	仅限女性员工
  --10018	授乳假	  仅限女性员工

  IF @leaveType IN ('10014', '10015', '10014', '10013', '10016', '10018')
  BEGIN
    INSERT INTO #result(user_serial, error_msg)
    SELECT user_serial, @leaveTypeName + '只能女性员工申请'
    FROM @userInfo
    WHERE user_sex = '男'

    DELETE FROM #leave
    WHERE user_serial IN (
        SELECT
          user_serial
        FROM @userInfo
        WHERE user_sex = '男'
      )
    DELETE FROM @userInfo
    WHERE user_sex = '男'
  END

  --10017	陪产假	仅限男性员工 
  IF @leaveType = '10017'
  BEGIN
    INSERT INTO #result(user_serial, error_msg)
    SELECT user_serial, @leaveTypeName + '只能男性员工申请'
    FROM @userInfo
    WHERE user_sex = '女'

    DELETE FROM #leave
    WHERE user_serial IN (
        SELECT
          user_serial
        FROM @userInfo
        WHERE user_sex = '女'
      )
    DELETE FROM @userInfo
    WHERE user_sex = '女'
  END


  --同一天不能申请相同假类的请假
  UPDATE lv
  SET same_type = 1
  FROM #leave lv
  INNER JOIN (
      SELECT
        detail.user_serial, detail.kssj, lv.leave_id
      FROM lr_kqingj detail
    INNER JOIN bd_leave_order lv ON lv.order_id = detail.order_id
      WHERE detail.kssj BETWEEN @startDate AND @endDate AND lv.is_del = 0 AND lv.status != 2
    ) x ON x.user_serial = lv.user_serial AND x.kssj = lv.leave_date AND x.leave_id = @leaveType

  --把同一天有同类假的人放入错误表
  INSERT INTO #result(user_serial, error_msg)
  SELECT DISTINCT user_serial, '同一天不能申请相同假类'
  FROM #leave
  WHERE ISNULL(same_type, 0) = 1

  DELETE FROM #leave
  WHERE ISNULL(same_type, 0) = 1

  --一天内请假总时长不能超过8小时
  UPDATE lv
  SET is_over = CASE WHEN x.total + lv.leave_duration > 480 THEN 1 ELSE 0 END 
  FROM #leave lv
  INNER JOIN (
      SELECT detail.user_serial, detail.kssj, SUM(jsjg) total
      FROM lr_kqingj detail
      INNER JOIN bd_leave_order lv ON lv.order_id = detail.order_id
      WHERE detail.kssj BETWEEN @startDate AND @endDate AND lv.is_del = 0
      GROUP BY detail.user_serial, detail.kssj
    ) x ON x.user_serial = lv.user_serial AND x.kssj = lv.leave_date AND x.leave_id = @leaveType

  INSERT INTO #result(user_serial, error_msg)
  SELECT DISTINCT user_serial, '一天内请假总时长不能超过8小时'
  FROM #leave
  WHERE ISNULL(is_over, 0) = 1

  DELETE FROM #leave
  WHERE ISNULL(is_over, 0) = 1


  --授乳假有效期验证
  IF @leaveType = '10018'
  BEGIN
    --更新有效期
    UPDATE lv
    SET lactational_date_begin = u.lactational_begin
       ,lactational_date_end = u.lactational_end
    FROM #leave lv
    INNER JOIN @userInfo u ON u.user_serial = lv.user_serial

    UPDATE #leave
    SET lactational_expire =
      CASE
        WHEN leave_date >= lactational_date_begin AND leave_date <= lactational_date_end THEN
          1
        ELSE
          0
      END

    INSERT INTO #result(user_serial, error_msg)
    SELECT user_serial, FORMATMESSAGE('%s授乳假已经过期', FORMAT(leave_date, 'yyyy-MM-dd')) FROM #leave WHERE lactational_expire = 0

    DELETE FROM #leave
    WHERE lactational_expire = 0
  END



  --请假额度
  UPDATE u
  SET duration_total = x.total
  FROM @userInfo u
  INNER JOIN (
      SELECT
        user_serial, SUM(leave_duration) total
      FROM #leave
      GROUP BY user_serial
    ) x ON x.user_serial = u.user_serial


  DECLARE @hasQuota INT
  SELECT @hasQuota = ISNULL(ishaveedu, 0) FROM kt_qingj WHERE bh = @leaveType

  --10001	年休假
  --10002	调休假
  --10018	授乳假

  --有额度的假验证额度
  IF @hasQuota IN (1, 2, 3)
  BEGIN
    --假期额度 年假、调休、授乳假
    UPDATE lv
    SET vacation_duration =
      CASE
                                                                      @leaveType
        WHEN '10001' THEN
          vacation_annual
        WHEN '10002' THEN
          rest_total
        WHEN '10018' THEN
          vacation_lactational
        ELSE
          0
      END
    FROM @userInfo lv
    INNER JOIN (
        SELECT
          user_serial, vacation_annual, vacation_lactational, rest_total
        FROM bd_view_kq_holidays_time_rowno
        WHERE row_no = 1
      ) ht ON ht.user_serial = lv.user_serial


    INSERT INTO #result(user_serial, error_msg)
    SELECT user_serial, @leaveTypeName + '假期额度不足'
    FROM @userInfo
    WHERE duration_total > vacation_duration

    DELETE FROM #leave
    WHERE user_serial IN (
        SELECT
          user_serial
        FROM @userInfo
        WHERE duration_total > vacation_duration
      )
    DELETE FROM @userInfo
    WHERE duration_total > vacation_duration
  END


  DECLARE @approveStatus INT = 0
  --放班不审批，审批状态自动审批

  IF @leaveType = '10003'
  BEGIN
    SET @approveStatus = 4

    INSERT INTO #result(user_serial, error_msg)
    SELECT user_serial, FORMAT(leave_date, 'yyyy-MM-dd') + '没有排班，不能放班'
    FROM #leave
    WHERE ISNULL(shift_id, 0) = 0
  END


  DELETE FROM @userInfo
  WHERE user_serial IN (
      SELECT
        user_serial
      FROM #result
    )
  DELETE FROM #leave
  WHERE user_serial IN (
      SELECT
        user_serial
      FROM #result
    )

  --有错误不生成申请单
  IF NOT EXISTS (
      SELECT
        1
      FROM #result
    )
  BEGIN
    DECLARE @peopleQty INT
           ,@totalTime INT
    SELECT @peopleQty = COUNT(1), @totalTime = SUM(duration_total)
    FROM @userInfo
    WHERE duration_total > 0

  BEGIN TRY
    BEGIN TRAN


    INSERT INTO bd_leave_order(order_id, order_no, leave_id, start_date, end_date, people_num, total_time, remark, creater_id, create_time, apply_type, status, is_del, begin_time, end_time, approver1, approver2)
    VALUES (@orderId, @orderNo, @leaveType, @startDate, @endDate, @peopleQty, @totalTime, @remark, @operateId, GETDATE(), @applyType, @approveStatus, 0, 
      dbo.bd_fn_numToMin(@startTime), dbo.bd_fn_numToMin(@endTime), @firstApprover, @secondApprover)


    INSERT INTO lr_kqingj(user_serial, bh, kssj, jssj, jsjg, czrq, order_id, whether_validity, settle_year, settle_month)
    SELECT DISTINCT user_serial, @leaveType, leave_date, leave_date, leave_duration, GETDATE(), @orderId, 1, cur_year, cur_month
    FROM #leave

    --扣除请假
    IF @hasQuota IN (1, 2, 3)
    BEGIN
      --有额度的假才生成明细
      DECLARE @json VARCHAR(MAX) = ''
      SELECT @json = (
         SELECT
           @orderId orderId, user_serial userSerial, CONVERT(VARCHAR(10), GETDATE(), 120) applyDate, duration_total deduct_usage
         FROM @userInfo
         WHERE duration_total > 0
         FOR JSON AUTO
       )

      --10001	年休假
      --10002	调休假
      --10018	授乳假
      DECLARE @vacationType INT = CASE
                                                                      @leaveType
        WHEN '10001' THEN
          1
        WHEN '10002' THEN
          10
        WHEN '10018' THEN
          20
        ELSE
          0
      END
--                   SELECT @json,@vacationType,@operateId
      EXEC bd_proc_vacation_deduction @json
                                     ,@vacationType
                                     ,@operateId
    END



    IF @leaveType = 10003
    BEGIN
      --放班请假成功后，更新排班信息
      UPDATE lv
      SET shift_name = FORMATMESSAGE('[F]%s|%s', bc.bh, bc.mc)
      FROM #leave lv
      INNER JOIN bd_view_bc_sd bc ON bc.bc_id = lv.shift_id

      DECLARE @sql VARCHAR(MAX) = ''
      SELECT @sql += FORMATMESSAGE('UPDATE kt_paiba SET d%d = ''%s'', gly_no = ''%I64d'' WHERE user_serial = %I64d AND rq = ''%s'';', 
        DAY(leave_date), shift_name, @operateId, user_serial, FORMAT(leave_date, 'yyyy-MM'))
      FROM #leave 
 
      EXEC(@sql)

      UPDATE pb
      SET shift_name = lv.shift_name, shift_is_rest = 2
      FROM bd_kq_schedule_detail pb
      INNER JOIN #leave lv ON pb.user_serial = lv.user_serial AND pb.shift_date = lv.leave_date
    END

    --重新计算考勤      
  	DECLARE @users VARCHAR(MAX) = ''
  	
  	SELECT DISTINCT
  		@users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
  	FROM @userInfo u
  
  	SET @users = SUBSTRING(@users,0, LEN(@users))
  	EXEC bd_proc_timecard_extend @startDate, @endDate, @users, 1

    COMMIT TRAN
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
    END;

    IF ISNULL(@errStr, '') != ''
    BEGIN
      INSERT INTO #result(user_serial, error_msg)
      SELECT 0, @errStr
    END

  END CATCH
  END


  UPDATE rt
  SET user_no = u.user_no
     ,user_name = u.user_lname
     ,dept_name = u.dep_name
  FROM #result rt
  INNER JOIN bd_view_user_all u ON u.user_serial = rt.user_serial


  SELECT DISTINCT user_no AS 'userNo',
   user_name AS 'userName',
   dept_name AS 'deptName',
   error_msg AS 'errorMsg'
  FROM #result

  DROP TABLE #leave
  DROP TABLE #result
  DROP TABLE #params

  SET NOCOUNT OFF;
END